﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using Model;
using IDAL;


//Oracle数据库访问实现类
namespace OracleDalLibrary
{
    public class SQLDal
    {
        private const string SQL_GET_USERNUM = "SELECT COUNT(*) FROM User";
        private const string SQL_INSERT_USER = "INSERT INTO User VALUES(:Uid, :Pwd, :Email, :Gender, :Nickname)";
        private const string PARM_USER_ID = ":Uid";
        private const string PARM_PWD = ":Pwd";
        private const string PARM_EMAIL = ":Email";
        private const string PARM_GENDER = ":Gender";
        private const string PARM_NICKNAME = ":Nickname";

        public void saveUser(User user)
        {
            int userId = 0;

            OracleParameter[] completeUserParms = null;
            OracleParameter[] userParms = GetUserParameters();
            OracleParameter statusParm = new OracleParameter(PARM_USER_ID, OracleType.Number);

            userParms[1].Value = user.Password;
            userParms[2].Value = user.Email;
            userParms[3].Value = user.Sex;
            userParms[4].Value = user.Name;

            using (OracleConnection conn = new OracleConnection(OracleHelper.ConnectionStringOrderDistributedTransaction))
            {
                conn.Open();

                userId = Convert.ToInt32(OracleHelper.ExecuteScalar(conn, CommandType.Text, SQL_GET_USERNUM)) + 1;

                userParms[0].Value = userId;

                statusParm.Value = userId;

                int numberOfParameters = userParms.Length + 1;

                completeUserParms = new OracleParameter[numberOfParameters];

                userParms.CopyTo(completeUserParms, 0);

                completeUserParms[userParms.Length] = statusParm;

                StringBuilder finalSQLQuery = new StringBuilder("BEGIN ");

                finalSQLQuery.Append(SQL_INSERT_USER);
                finalSQLQuery.Append("; ");
                finalSQLQuery.Append("END;");

                OracleHelper.ExecuteNonQuery(conn, CommandType.Text, finalSQLQuery.ToString(), completeUserParms);
            }
        }
        public void modifyUser(User user) { }
        public void deleteUser(User user) { }
        public User getUser(int id) { return null; }
        public User getUser(string email) { return null; }
        public IList<User> getUser() { return null; }

        private static OracleParameter[] GetUserParameters()
        {
            OracleParameter[] parms = OracleHelper.GetCachedParameters(SQL_INSERT_USER);

            if (parms == null)
            {
                parms = new OracleParameter[] {												 
					new OracleParameter(PARM_USER_ID, OracleType.Int32),
					new OracleParameter(PARM_PWD, OracleType.VarChar, 1),
					new OracleParameter(PARM_EMAIL, OracleType.VarChar, 1),
					new OracleParameter(PARM_GENDER, OracleType.Int16),
					new OracleParameter(PARM_NICKNAME, OracleType.VarChar, 1),
					};

                OracleHelper.CacheParameters(SQL_INSERT_USER, parms);
            }

            return parms;
        }
    }
}
